﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
    public class MonHoc
    {
        #region khởi tạo
        private string _maMH;
        public string MaMH
        {
            get { return _maMH; }
            set { _maMH = value; }
        }

        private string _tenMH;
        public string TenMH
        {
            get { return _tenMH; }
            set { _tenMH = value; }
        }

        int _SoTC;
        public int SoTC
        {
            get { return _SoTC; }
            set { _SoTC = value; }
        }

        int _TCLT;
        public int TCLT
        {
            get { return _TCLT; }
            set { _TCLT = value; }
        }

        int _TCTH;
        public int TCTH
        {
            get { return _TCTH; }
            set { _TCTH = value; }
        }

        bool _BatBuoc;
        public bool BatBuoc
        {
            get { return _BatBuoc; }
            set { _BatBuoc = value; }
        }

        string _MoTaMH;
        public string MoTaMH
        {
            get { return _MoTaMH; }
            set { _MoTaMH = value; }
        }

        public MonHoc() { }
        public MonHoc(string ma, string ten)
        {
            MaMH = ma;
            TenMH = ten;
        }

        public MonHoc(MonHoc mh)
        {
            MaMH = mh.MaMH;
            TenMH = mh.TenMH;
        }
        #endregion

        private static SqlCommand cmd;
        private static SqlConnection cnn;
        private static void MyCommand(string text)
        {
            cmd = cnn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = text;
        }
        public static MonHoc GetData(SqlDataReader reader)
        {
            MonHoc data = new MonHoc();
            data.MaMH = CType.Tostring(reader[0]);
            data.TenMH = CType.Tostring(reader[1]);
            data.SoTC = CType.ToInt32(reader[2]);
            data.TCLT = CType.ToInt32(reader[3]);
            data.TCTH = CType.ToInt32(reader[4]);
            data.BatBuoc = CType.ToBool(reader[5]);
            data.MoTaMH = CType.Tostring(reader[6]);
            return data;
        }

        public static List<MonHoc> All()
        {
            List<MonHoc> list = new List<MonHoc>();
            using (cnn = MyConnection.MyConn())
            {
                MyCommand("MonHoc_All");
                cnn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        MonHoc data = GetData(reader);
                        list.Add(data);
                    }
                }
            }
            return list;
        }
        public static int Add(MonHoc mh)
        {
            using (SqlConnection connec = MyConnection.MyConn())
            {
                MyCommand("MonHoc_Add");
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = mh.MaMH;
                cmd.Parameters.Add("@TenMH", SqlDbType.VarChar, 200).Value = mh.TenMH;

                connec.Open();
                int num = cmd.ExecuteNonQuery();
                return num;
            }
        }

        public static string Them(MonHoc data)
        {
            using (cnn = MyConnection.MyConn())
            {
                MyCommand("MonHoc_Add");
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = data.MaMH;
                cmd.Parameters.Add("@TenMH", SqlDbType.NVarChar, 200).Value = data.TenMH;
                cmd.Parameters.Add("@SoTC", SqlDbType.Int).Value = data.SoTC;
                cnn.Open();
                int rs = (int)cmd.ExecuteNonQuery();
                cnn.Close();
                if (rs > 0)
                    return (string)cmd.Parameters["@MaMH"].Value;
                else
                    return "";
            }


        }

        public static int Xoa(string ma)
        {
            using (cnn = MyConnection.MyConn())
            {
                MyCommand("MonHoc_Delete");
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = ma;
                cnn.Open();
                int rs = (int)cmd.ExecuteNonQuery();
                cnn.Close();
                if (rs > 0)
                    return rs;
                else
                    return 0;
            }
        }

        public static int Sua(MonHoc data)
        {
            using (cnn = MyConnection.MyConn())
            {
                MyCommand("MonHoc_Update");
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = data.MaMH;
                cmd.Parameters.Add("@TenMH", SqlDbType.NVarChar, 200).Value = data.TenMH;
                cmd.Parameters.Add("@SoTC", SqlDbType.Int).Value = data.SoTC;
                cnn.Open();
                int rs = (int)cmd.ExecuteNonQuery();
                cnn.Close();
                if (rs > 0)
                    return rs;
                else return 0;
            }
        }

        public static MonHoc TimMHTheoMa(string ma)
        {
            using (cnn = MyConnection.MyConn())
            {
                MyCommand("MonHoc_LocTheoMa");
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = ma;
                cnn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        MonHoc data = GetData(reader);
                        return data;
                    }
                    else return null;
                }
            }
        }

        public static List<MonHoc> Find_ByName(string name)
        {
            List<MonHoc> list = new List<MonHoc>();
            using (SqlConnection connec = MyConnection.MyConn())
            {
                MyCommand("MonHoc_FindByName");
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = name;
                connec.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        MonHoc mh = GetData(reader);
                        list.Add(mh);
                    }
                }

            }
            return list;
        }

        public static List<MonHoc> LocMHTheoTen(string name)
        {
            List<MonHoc> list = new List<MonHoc>();
            using (cnn = MyConnection.MyConn())
            {
                cmd = cnn.CreateCommand();
                cmd.CommandText = "select * from MonHoc where TenMH like N'%" + name + "%'";
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MonHoc mh = GetData(reader);
                    list.Add(mh);
                }

            }
            return list;
        }
        public static MonHoc LocMHTheoMa(string ma)
        {
            MonHoc mh = new MonHoc();
            using (cnn = MyConnection.MyConn())
            {
                MyCommand("MonHoc_LocTheoMa");
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = ma;
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                  mh = GetData(reader);
            }
            return mh;
        }
        public static List<MonHoc> XemDiem(string mssv)
        {
            List<MonHoc> list = new List<MonHoc>();
            using (cnn = MyConnection.MyConn())
            {
                cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "QuanLyDangKyHocPhan_LayDanhSachNamDaHoc";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MonHoc data = GetData(reader);
                    list.Add(data);
                }
                return list;
            }
        }
        public static List<MonHoc> XemMonHocTheoNamHoc(string Namhoc)
        {
            try
            {
	            List<MonHoc> list = new List<MonHoc>();
	            using (cnn = MyConnection.MyConn())
	            {
	                cmd = cnn.CreateCommand();
	                cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "QuanLyDangKyHocPhan_XemHocPhanTheoNamHoc";
	                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = Namhoc;
	                cnn.Open();
	                SqlDataReader reader = cmd.ExecuteReader();
	                while (reader.Read())
	                {
	                    MonHoc data = GetData(reader);
	                    list.Add(data);
	                }
	                return list;
	            }
            }
            catch (System.Exception ex)
            {
                return null;
            }
        }
        public static List<MonHoc> MonHoc_XemTheoKeHoachDaoTao(int nhom,int nam,int MaHK,string namHoc)
        {
            List<MonHoc> list = new List<MonHoc>();
            using (cnn = MyConnection.MyConn())
            {
                cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "MonHoc_XemTheoKeHoachDaoTao";
                cnn.Open();
                cmd.Parameters.Add("@Nhom", SqlDbType.Int).Value = nhom;
                cmd.Parameters.Add("@Nam", SqlDbType.Int).Value = nam;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = MaHK;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namHoc;
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        MonHoc data = GetData(reader);
                        list.Add(data);
                    }
                }
            }
            return list;
        }
        public static List<MonHoc> MonHoc_LayDSMonHocTheoLop(string malop, int hk, string namHoc)
        {
            List<MonHoc> list = new List<MonHoc>();
            using (cnn = MyConnection.MyConn())
            {
                cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "MonHoc_LayDSMonHocTheoLop";
                cnn.Open();
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = malop;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namHoc;
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        MonHoc data = GetData(reader);
                        list.Add(data);
                    }
                }
            }
            return list;
        }
        public static List<MonHoc> MonHoc_LayDSMonHocTheoGV(string magv, int hk, string namHoc)
        {
            List<MonHoc> list = new List<MonHoc>();
            using (cnn = MyConnection.MyConn())
            {
                cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "MonHoc_LayDSMonHocTheoGV";
                cnn.Open();
                cmd.Parameters.Add("@MaGV", SqlDbType.VarChar, 10).Value = magv;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namHoc;
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        MonHoc data = GetData(reader);
                        list.Add(data);
                    }
                }
            }
            return list;
        }
    }
}
